JSONTOXML\(rootElementName, json)
XML functions provide functionality for working with XML data.
Returns an xml document from JSON.
JSONTOXML\(rootElementName, json)
rootElementName is a string, json is in {clob, blob}. Return value is xml.
The appropriate UTF encoding (8, 16LE. 16BE, 32LE, 32BE) will be detected for JSON blobs. If another encoding is used, see the to_chars function.
The result is always a well-formed XML document.
The mapping to XML uses the following rules:
The current element name is initially the rootElementName, and becomes the object value name as the JSON structure is traversed.
All element names must be valid xml 1.1 names. Invalid names are fully escaped according to the SQLXML specification.
Each object or primitive value will be enclosed in an element with the current name.
Unless an array value is the root, it will not be enclosed in an additional element.
Null values will be represented by an empty element with the attribute xsi:nil="true"
JSON:
"firstName" : "John" , "children" : [ "Randy", "Judy" ]
XML:
<?xml version="1.0" ?>
<person>
<firstName>John</firstName>
<children>Randy</children>
<children>Judy<children>
</person>
JSON:
[{"firstName" : "George" }, { "firstName" : "Jerry" }]
XML (Notice there is an extra "person" wrapping element to keep the XML well-formed):
<?xml version="1.0" ?>
<person>
<person>
<firstName>George</firstName>
</person>
<person>
<firstName>Jerry</firstName>
</person>
</person>
Returns an xml comment.
XMLCOMMENT(comment)
Comment is a string. Return value is xml.
Returns an XML with the concatination of the given xml types.
XMLCONCAT(content [, content]*)
Content is xml. Return value is xml.
If a value is null, it will be ignored. If all values are null, null is returned.
Returns an XML element with the given name and content.
XMLELEMENT([NAME] name [, <NSP>] [, <ATTR>][, content]*) ATTR:=XMLATTRIBUTES(exp [AS name] [, exp [AS name]]*) NSP:=XMLNAMESPACES((uri AS prefix | DEFAULT uri | NO DEFAULT))+
If the content value is of a type other than xml, it will be escaped when added to the parent element. Null content values are ignored. Whitespace in XML or the string values of the content is preserved, but no whitespace is added between content values.
XMLNAMESPACES is used provide namespace information. NO DEFAULT is equivalent to defining the default namespace to the null uri - xmlns="". Only one DEFAULT or NO DEFAULT namespace item may be specified. The namespace prefixes xmlns and xml are reserved.
If a attribute name is not supplied, the expression must be a column reference, in which case the attribute name will be the column name. Null attribute values are ignored.
Name, prefix are identifiers. uri is a string literal. content can be any type. Return value is xml. The return value is valid for use in places where a document is expected.
with an xml_value of <doc/>
XMLELEMENT(NAME "elem", 1, '<2/>', xml_value)
Returns:
<elem>1<2><doc/><elem/>
Returns an concatination of XML elements for each content item.
XMLFOREST(content [AS name] [, <NSP>] [, content [AS name]]*)
See XMLELEMENT for the definition of NSP - XMLNAMESPACES
Name is an identifier. Content can be any type. Return value is xml.
If a name is not supplied for a content item, the expression must be a column reference, in which case the element name will be a partially escaped version of the column name.
Returns an XML type representation of the string value expression.
XMLPARSE((DOCUMENT|CONTENT) expr [WELLFORMED])
expr in {string, clob, blob}. Return value is xml.
If DOCIMENT is specified then the expression must have a single root element and may or may not contain an XML declaration.
If WELLFORMED is specified then validation is skipped; this is especially useful for CLOB and BLOB known to already be valid.
Returns an xml processing instruction.
XMLPI([NAME] name [, content])
Name is an identifier. Content is a string. Return value is xml.
Returns the XML result from evaluating the given xquery.
XMLQUERY([<NSP>] xquery [<PASSING>] [(NULL|EMPTY) ON EMPTY]] PASSING:=PASSING exp [AS name] [, exp [AS name]]*
See XMLELEMENT for the definition of NSP - XMLNAMESPACES
Namespaces may also be directly declared in the xquery prolog.
The optional PASSING clause is used to provide the context item, which does not have a name, and named global variable values. If the xquery uses a context item and none is provided, then an exception will be raised. Only one context item may be specified and should be an XML type. All non-context non-XML passing values will be converted to an appropriate XML type.
The ON EMPTY clause is used to specify the result when the evaluted sequence is empty. EMPTY ON EMPTY, the default, returns an empty XML result. NULL ON EMPTY returns a null result.
xquery in string. Return value is xml.
XMLQUERY is part of the SQL/XML 2006 specification.
See also FROM Clause#XMLTABLE
See also XQuery Optimization
Returns a character type representation of the xml expression.
XMLSERIALIZE([(DOCUMENT|CONTENT)] xml [AS datatype])
Return value matches datatype.
Only a character type (string, varchar, clob) may be specified as the datatype. CONTENT is the default. If DOCUMENT is specified and the xml is not a valid document or fragment, then an exception is raised.
Applies an XSL stylesheet to the given document.
XSLTRANSFORM(doc, xsl)
Doc, xsl in {string, clob, xml}. Return value is a clob.
If either argument is null, the result is null.
Applies the XPATH expression to the document and returns a string value for the first matching result.
XPATHVALUE(doc, xpath)
Doc and xpath in {string, clob, xml}. Return value is a string.
Matching a non-text node will still produce a string result, which includes all descendent text nodes.
XML value:
<?xml version="1.0" ?> <ns1:return xmlns:ns1="http://com.test.ws/exampleWebService">Hello<x> World</x></return>
Function:
xpathValue(value, '/*[local-name()="return"])
Results in 'Hello World'
See also XMLQUERY